IF OBJECT_ID('dbo.RptAgrDemographicSummaryErrorTrend') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptAgrDemographicSummaryErrorTrend
    IF OBJECT_ID('dbo.RptAgrDemographicSummaryErrorTrend') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptAgrDemographicSummaryErrorTrend >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptAgrDemographicSummaryErrorTrend >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptAgrDemographicSummaryErrorTrend
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 01/22/09
--
-- Description: Source of Report, "AgrDemographicSummaryErrorTrend"
--                  In Insight DB Reporting Services
--
-- Parameters:  
-- 
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptAgrDemographicSummaryErrorTrend('1/22/2009', 6)
--
-----------------------------------------------------------------------------------
-- Date        Initials  Modification
------------------------------------------------------------------------------------
-- 01/22/09    KC        Created
-- 01/23/09    KC        Switched to use Detail table, and temp table..
--                       finalized, all fields working.
--
-- ==================================================================================

CREATE PROCEDURE dbo.RptAgrDemographicSummaryErrorTrend
     (@LogDate       DATETIME = NULL,
      @ProjEndDate   DATETIME = NULL,
      @CompareBaseDate DATETIME =NULL)
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error         INT,
               @RowCount      INT
                  
     
     DECLARE   @LogDates TABLE
               (WeekNbr       INT,
                LogDate        DATETIME)
          
     DECLARE   @ActiveCounts TABLE
          (ErrorCode          VARCHAR(200)   NOT NULL,
          MGHWeek0            INT            NULL,
          MGHWeek1            INT            NULL,
          MGHWeek2            INT            NULL,
          MGHWeek3            INT            NULL,
          MGHStart			  int            NULL,
          MGHChgFromStart     INT            NULL,
          BWHWeek0            INT            NULL,
          BWHWeek1            INT            NULL,
          BWHWeek2            INT            NULL,
          BWHWeek3            INT            NULL,
          BWHStart            int            NULL,
          BWHChgFromStart     INT            NULL)
          
          
     -- If NULL, get last date
     IF @LogDate IS NULL
          SELECT @LogDate = MAX(LogDate)
          FROM   dbo.Stg_AgrDemographicValidataionLog

     -- Check For Project End
     IF @ProjEndDate IS NULL SET @ProjEndDate = '8/1/2008' 

	 -- Check For Compare Base Date
     IF @CompareBaseDate IS NULL SET @CompareBaseDate = '10/31/2008' 
    
     -- Get dates
     INSERT    @LogDates
          (WeekNbr,
          LogDate)
     SELECT    0,
               @LogDate
     UNION
     SELECT    1,
               DATEADD(DAY,-7,@LogDate)
     UNION
     SELECT    2,
               DATEADD(DAY,-14,@LogDate)
     UNION     
     SELECT    3,
               DATEADD(DAY,-21,@LogDate)
     UNION 
     SELECT    4,
               @CompareBaseDate

     -- Populate table, we'll need to summarize, etc.
     INSERT    @ActiveCounts
               (ErrorCode,
               MGHWeek0,
               MGHWeek1,
               MGHWeek2,
               MGHWeek3,
               MGHStart,
               MGHChgFromStart,
               BWHWeek0,
               BWHWeek1,
               BWHWeek2,
               BWHWeek3,
               BWHStart,
               BWHChgFromStart)
     SELECT    ErrorCode,
               SUM(CASE WHEN WeekNbr = 0
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHWeek0,
               SUM(CASE WHEN WeekNbr = 1
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHWeek1,
               SUM(CASE WHEN WeekNbr = 2
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHWeek2,
               SUM(CASE WHEN WeekNbr = 3
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHWeek3,
               SUM(CASE WHEN WeekNbr = 4
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHStart,  
               SUM(CASE WHEN WeekNbr = 0
                    THEN MGHActiveCnt
                    ELSE 0
               END) - SUM(CASE WHEN WeekNbr = 4
                         THEN MGHActiveCnt
                         ELSE 0
                    END) AS MGHChgFromStart,
               SUM(CASE WHEN WeekNbr = 0
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHWeek0,
               SUM(CASE WHEN WeekNbr = 1
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHWeek1,
               SUM(CASE WHEN WeekNbr = 2
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHWeek2,
               SUM(CASE WHEN WeekNbr = 3
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHWeek3,
               SUM(CASE WHEN WeekNbr = 4
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHStart,
               SUM(CASE WHEN WeekNbr = 0
                    THEN BWHActiveCnt
                    ELSE 0
               END) - SUM(CASE WHEN WeekNbr = 4
                         THEN BWHActiveCnt
                         ELSE 0
                    END) AS BWHChgFromStart
     FROM (
          SELECT    D.WeekNbr,
                    E.ErrorCode,
                    COUNT(CASE WHEN E.BusinessUnit = 1200
                         THEN E.FolderNumber
                         ELSE NULL
                    END) AS MGHActiveCnt,
                    COUNT(CASE WHEN E.BusinessUnit = 2200
                         THEN E.FolderNumber
                         ELSE NULL
                    END) AS BWHActiveCnt
          FROM      @LogDates D
                    LEFT OUTER JOIN dbo.Stg_AgrDemographicValidataionErrorDetail E ON D.LogDate = E.LogDate
        WHERE ((E.ErrorCode= 'FAILURE_COMPOSITE_CHECK_INFOED') OR
			  (E.BusinessUnit IN (1200,2200) AND -- MGH = 1200, BWH = 2200 only
			   E.Status = 'A' AND -- Active Only
               E.PSProjectEnd > @ProjEndDate AND  -- Check ProjectEndDate
               E.ErrorCode IS NOT NULL AND E.ErrorCode != '')) -- Error Code should not be empty
     GROUP BY  D.WeekNbr,
               E.ErrorCode ) CNT
     WHERE     ErrorCode IS NOT NULL           
     GROUP BY ErrorCode
     --ORDER BY ErrorCode

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     RETURN -1

     -- Total
     INSERT    @ActiveCounts
               (ErrorCode,
               MGHWeek0,
               MGHWeek1,
               MGHWeek2,
               MGHWeek3,
               MGHStart,
               MGHChgFromStart,
               BWHWeek0,
               BWHWeek1,
               BWHWeek2,
               BWHWeek3,
               BWHStart,
               BWHChgFromStart)
     SELECT    'Total Errors',
               SUM(MGHWeek0),
               SUM(MGHWeek1),
               SUM(MGHWeek2),
               SUM(MGHWeek3),
               SUM(MGHStart),
               SUM(MGHChgFromStart),
               SUM(BWHWeek0),
               SUM(BWHWeek1),
               SUM(BWHWeek2),
               SUM(BWHWeek3),
               SUM(BWHStart),
               SUM(BWHChgFromStart)
     FROM      @ActiveCounts

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     RETURN -1

     -- This is a count of distinct FundNumbers
     INSERT    @ActiveCounts
               (ErrorCode,
               MGHWeek0,
               MGHWeek1,
               MGHWeek2,
               MGHWeek3,
               MGHStart,
               MGHChgFromStart,
               BWHWeek0,
               BWHWeek1,
               BWHWeek2,
               BWHWeek3,
               BWHStart,
               BWHChgFromStart)
     SELECT    'Total Unique Fund Numbers',
               SUM(CASE WHEN WeekNbr = 0
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHWeek0,
               SUM(CASE WHEN WeekNbr = 1
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHWeek1,
               SUM(CASE WHEN WeekNbr = 2
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHWeek2,
               SUM(CASE WHEN WeekNbr = 3
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHWeek3,
               SUM(CASE WHEN WeekNbr = 4
                    THEN MGHActiveCnt
                    ELSE 0
               END) AS MGHStart,
               SUM(CASE WHEN WeekNbr = 0
                    THEN MGHActiveCnt
                    ELSE 0
               END) - SUM(CASE WHEN WeekNbr = 4
                         THEN MGHActiveCnt
                         ELSE 0
                    END) AS MGHChgFromStart,
               SUM(CASE WHEN WeekNbr = 0
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHWeek0,
               SUM(CASE WHEN WeekNbr = 1
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHWeek1,
               SUM(CASE WHEN WeekNbr = 2
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHWeek2,
               SUM(CASE WHEN WeekNbr = 3
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHWeek3,
               SUM(CASE WHEN WeekNbr = 4
                    THEN BWHActiveCnt
                    ELSE 0
               END) AS BWHStart,
               SUM(CASE WHEN WeekNbr = 0
                    THEN BWHActiveCnt
                    ELSE 0
               END) - SUM(CASE WHEN WeekNbr = 4
                         THEN BWHActiveCnt
                         ELSE 0
                    END) AS BWHChgFromStart
     FROM (
          SELECT    D.WeekNbr,
                    COUNT(DISTINCT CASE WHEN E.BusinessUnit = 1200
                         THEN E.PSFundNumber
                         ELSE NULL
                    END) AS MGHActiveCnt,
                    COUNT(DISTINCT CASE WHEN E.BusinessUnit = 2200
                         THEN E.FolderNumber
                         ELSE NULL
                    END) AS BWHActiveCnt          
          FROM      @LogDates D
                    LEFT OUTER JOIN dbo.Stg_AgrDemographicValidataionErrorDetail E ON D.LogDate = E.LogDate
         WHERE ((E.ErrorCode= 'FAILURE_COMPOSITE_CHECK_INFOED') OR
				(E.BusinessUnit IN (1200,2200) AND -- MGH = 1200, BWH = 2200 only
				 E.Status = 'A' AND -- Active Only
				 E.PSProjectEnd > @ProjEndDate AND  -- Check ProjectEndDate
				 E.ErrorCode IS NOT NULL AND E.ErrorCode != '')) -- Error Code should not be empty 
         GROUP BY  D.WeekNbr ) CNT

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     RETURN -1


     -- Return Result set
     SELECT    D.Week0Date,
               D.Week1Date,
               D.Week2Date,
               D.Week3Date,
               D.CompareDate,
               @ProjEndDate AS ProjectEndDateCutOff,
               ErrorCode,
               MGHWeek0,
               MGHWeek1,
               MGHWeek2,
               MGHWeek3,
               MGHStart,
               MGHChgFromStart,
               BWHWeek0,
               BWHWeek1,
               BWHWeek2,
               BWHWeek3,
               BWHStart,
               BWHChgFromStart
     FROM      @ActiveCounts
               FULL OUTER JOIN
                    (SELECT   MAX(CASE WHEN WeekNbr = 0
                                   THEN LogDate 
                                   ELSE '1/1/1900'
                              END) AS Week0Date,
                              MAX(CASE WHEN WeekNbr = 1
                                   THEN LogDate 
                                   ELSE '1/1/1900'
                              END) AS Week1Date,
                              MAX(CASE WHEN WeekNbr = 2
                                   THEN LogDate 
                                   ELSE '1/1/1900'
                              END) AS Week2Date,
                              MAX(CASE WHEN WeekNbr = 3
                                   THEN LogDate 
                                   ELSE '1/1/1900'
                              END) AS Week3Date,
                              MAX(CASE WHEN WeekNbr = 4
                                   THEN LogDate 
                                   ELSE '1/1/1900'
                              END) AS CompareDate
                    FROM      @LogDates ) D ON 1=1
                              
                              
     ORDER BY ErrorCode



     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptAgrDemographicSummaryErrorTrend') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptAgrDemographicSummaryErrorTrend >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptAgrDemographicSummaryErrorTrend >>>'
go

GRANT EXECUTE ON dbo.RptAgrDemographicSummaryErrorTrend TO InsightReport,Insight
go


